package quantri.qlcn;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import utils.UtilsOnScopes;

import dbutil.ConnectDB;

/******************************************
 * Filename			: Function.java
 * Decription		: Bean cho chức năng quản lý chức năng
 * Created date		: Jun 4, 2008
 * Author			: Phonglt
 ******************************************/
public class Function {
	private int id;
	private int group;
	private int cat;
	private String folder ="";
	private String name ="";
	private String error ="";
	private List<FuncTable> list;
	
	/**************************************
	 * Method name		: save
	 * Return type		: String
	 * Decription		: Luu du lieu cua tao moi chuc nang
	 * Created date		: Jun 15, 2008
	 * Author			: Phonglt
	 * Modified			:honglk(5-7-2008)
	 **************************************/
	public String save() {
		try {
			String sqlCheckFolder = "  SELECT folder FROM dschucnang  ";
			if (this.folder != null)
				ConnectDB.connectDB();
				sqlCheckFolder += "WHERE folder = ? AND groupid = ?";
			PreparedStatement prestmt1 = ConnectDB.connect
					.prepareStatement(sqlCheckFolder);
			prestmt1.setString(1, this.folder);
			prestmt1.setInt(2, this.group);

			ResultSet kq = prestmt1.executeQuery();
			if (kq.next()) {
				this.setError("<b>Thư mục này đã tồn tại.</b> <br/> ***Trong một nhóm thì các thư mục phải khác nhau");
				return "";	
			}
			else if(this.name == null || this.name.equals("")){
				this.setError("<b>Chưa nhập tên chức năng.</b> <br/>");
				return "";	
				
			}
			else if(this.folder == null || this.folder.equals("")){
				this.setError("<b>Chưa nhập tên thư mục.</b> <br/>");
				return "";	
			}
			else if(this.cat == -1){
				this.setError("<b>Chưa chọn loại.</b> <br/>");
				return "";	
			}
			else if(this.group == -1){
				this.setError("<b>Chưa chọn nhóm.</b> <br/>");
				return "";	
			}
			else {
				PreparedStatement prestmt = ConnectDB.connect
						.prepareStatement("INSERT INTO dschucnang(groupid, catid, fullname, folder) VALUES(?,?,?,?)");
				prestmt.setInt(1, this.group);
				prestmt.setInt(2, this.cat);
				prestmt.setString(3, this.name);
				prestmt.setString(4, this.folder);
				prestmt.executeUpdate();
				this.setError("<b>Bạn đã thêm chức năng mới thành công.</b>");
				this.folder = "";
				this.name = "";
				return "qlcn";
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return "qlcn";
	}

	
	/**************************************
	 * Method name		: delete
	 * Return type		: String
	 * Decription		: xoa chuc nang
	 * Created date		: Jun 15, 2008
	 * Author			: Phonglt
	 **************************************/
	public String delete(){
		HttpServletRequest req = UtilsOnScopes.getRequest();
		String deletedId = req.getParameter("id").trim();
		String name = req.getParameter("name").trim();
		if (deletedId.equals(null)) {
			this.setError("<b>Lỗi xóa chức năng.</b>");
		} else {
			String sql = "DELETE FROM dschucnang WHERE id = ?";
			System.out.println("delete ds chuc nang "+sql);
			try {
				ConnectDB.connectDB();
				PreparedStatement stmt =ConnectDB.connect 
						.prepareStatement(sql);
				stmt.setString(1, deletedId);
				stmt.execute();
				this.setError("Bạn đã xóa thành công một chức năng \"<b>" + name.trim() + "\"</b>.");
			} catch (SQLException e) {
				this.setError("<b>Lỗi truy xuất Cơ sở dữ liệu</b>");
				e.printStackTrace();
			}
			this.reset();
		}
		return "qlcn";
	}
	
	private void reset() {
		// TODO Auto-generated method stub
		this.folder = "";
		this.name = "";
	}

	/**************************************
	 * Method name		: edit
	 * Return type		: String
	 * Decription		: sua chua du lieu cua chuc nang
	 * Created date		: Jun 15, 2008
	 * Author			: Phonglt
	 * Modified			: Honglk
	 **************************************/
	public String edit(){
		HttpServletRequest req = UtilsOnScopes.getRequest();
		String deletedId = req.getParameter("id");
		String name = req.getParameter("name");
		String folder = req.getParameter("folder");
		String groupid = req.getParameter("groupid");
		String catid = req.getParameter("catid");
		
		this.setError("");
		this.setId(Integer.valueOf(deletedId));
		this.setName(name.trim());
		this.setFolder(folder.trim());
		this.setGroup(Integer.parseInt(groupid));
		this.setCat(Integer.parseInt(catid));
//		return "qlcn_edit";
		return "qlcn";
	}
	
	/**************************************
	 * Method name		: saveEdit
	 * Return type		: String
	 * Decription		:
	 * Created date		: Jun 22, 2008
	 * Author			: Phonglt
	 **************************************/
	public String saveEdit(){
		try {
			String sqlCheckFolder = "  UPDATE dschucnang SET groupid=?, catid=?, fullname=?,folder=? " +
					" WHERE id = ?";
			if (this.id == 0) {
				this.setError("Không tìm thấy ID của chức năng này");
				return "qlcn";
			}
			PreparedStatement prestmt1 = ConnectDB.connect.prepareStatement(sqlCheckFolder);
			prestmt1.setInt(1, this.group);
			prestmt1.setInt(2, this.cat);
			prestmt1.setString(3, this.name);
			prestmt1.setString(4, this.folder);
			prestmt1.setInt(5, this.id);

			if(prestmt1.executeUpdate() < 1){
				this.setError("Lỗi ghi vào cơ sở dữ liệu.");
			} else 
				this.setError("Đã cập nhật thành công chức năng \"<b>" + this.name + "\"</b>.");
			this.reset();
			
		} catch (SQLException e) {
			e.printStackTrace();
			this.setError("Lỗi Cơ sở dữ liệu của chức năng");
		}
		return "qlcn";
	}
	
	
	/**************************************
	 * Method name		: dataOfFuncs
	 * Return type		: String
	 * Decription		:
	 * Created date		: Jul 6, 2008
	 * Author			: Phonglt
	 * Modified			:Honglk
	 **************************************/
	public String dataOfFuncs(){
		List<FuncTable> funcs = new ArrayList<FuncTable>();
		String sql = "SELECT C.fullname as groupname, D.fullname as catname, B.fullname as name, B.folder, B.id, B.groupid, B.catid " +
				"FROM dschucnang B, nhomchucnang C, loaichucnang D " +
				"WHERE B.groupid = C.id AND B.catid = D.id AND " +
				this.searchHelp(this.name, this.folder, String.valueOf(this.group), String.valueOf(this.cat)) +
				" ORDER BY B.groupid, B.catid, B.fullname";
		System.out.println(sql);
		ArrayList<HashMap<String, Object>> arr = ConnectDB.getDataTypeList(sql);
		if (arr.size() > 0) {
		for (int i = 0; i < arr.size(); i++) {
			HashMap<String, Object> row = arr.get(i);
			String id = Integer.toString((Integer) row.get("id"));
			FuncTable func = new FuncTable(id, String.valueOf(i + 1),
					(String) row.get("groupname"),
					(Integer) row.get("groupid"), (String) row.get("catname"),
					(Integer) row.get("catid"), (String) row.get("name"),
					(String) row.get("folder"));
			funcs.add(func);
		}
	 }
		this.setList(funcs);
		return "qlcn";
	}
	
	public String searchHelp(String funcName, String folder,String groupId,String catid) {
		StringBuffer buffer = new StringBuffer();
		String strTemp1 = "";
		String strTemp2= "";
		String strTempCond1 = "TRIM(LOWER(B.fullname)) LIKE TRIM(LOWER('%" + funcName
				+ "%')) OR '" + (funcName.equals("") ? "1" : funcName)
				+ "'='1'";
		String strTempCond2 = "TRIM(LOWER(B.folder)) LIKE TRIM(LOWER('%" + folder
		+ "%')) OR '" + (folder.equals("") ? "1" : folder)
		+ "'='1'";
		String strTempCond3 = "catid = '" + catid + "' OR '"
		+ (catid.equals("-1") ? "0" : catid) + "'= '0'";
		
		String strTempCond4 = "groupid = '" + groupId + "' OR '"
				+ (groupId.equals("-1") ? "0" : groupId) + "'= '0'";

		strTemp1 = "(" + strTempCond1 + ") AND (" + strTempCond2 + ")";

		strTemp2 = "(" + strTempCond3 + ") AND (" + strTempCond4 + ") ";
		String strTemp = strTemp1 + " AND " +strTemp2;
		buffer.append("(");
		buffer.append(strTemp);
		buffer.append(")");
		System.out.println("catId:"+catid);
		System.out.println("goupId:"+groupId);
		return buffer.toString();
	}
	public static void main(String[] args) {
		Function f = new Function();
//		System.out.println(f.searchHelp("gf", "fgf", "2", "3"));
		f.setName("t");
		f.setFolder("f");
		f.setCat(2);
		f.setGroup(1);
		f.dataOfFuncs();
	}
	
	public String cancelEdit(){
		this.setId(0);
		this.setName("");
		this.setGroup(0);
		this.setCat(0);
		this.setError("");
		this.setFolder("");
		return "qlcn";
	}
	
	/****************************************************
	 * Method name		: getError [GETTER]
	 * Return type		: Function
	 * Decription		: 
	 * Created date		: Jun 15, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public String getError() {
		return error;
	}


	/****************************************************
	 * Method name		: setError [SETTER]
	 * Return type		: void
	 * Decription		: 
	 * Created date		: Jun 15, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setError(String error) {
		this.error = error;
	}


	/****************************************************
	 * Method name		: getGroup [GETTER]
	 * Return type		: Function
	 * Decription		: 
	 * Created date		: Jun 4, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public int getGroup() {
		return group;
	}
	/****************************************************
	 * Method name		: setGroup [SETTER]
	 * Return type		: void
	 * Decription		: 
	 * Created date		: Jun 4, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setGroup(int group) {
		this.group = group;
	}
	/****************************************************
	 * Method name		: getCat [GETTER]
	 * Return type		: Function
	 * Decription		: 
	 * Created date		: Jun 4, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public int getCat() {
		return cat;
	}
	/****************************************************
	 * Method name		: setCat [SETTER]
	 * Return type		: void
	 * Decription		: 
	 * Created date		: Jun 4, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setCat(int cat) {
		this.cat = cat;
	}
	/****************************************************
	 * Method name		: getFolder [GETTER]
	 * Return type		: Function
	 * Decription		: 
	 * Created date		: Jun 4, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public String getFolder() {
		return folder;
	}
	/****************************************************
	 * Method name		: setFolder [SETTER]
	 * Return type		: void
	 * Decription		: 
	 * Created date		: Jun 4, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setFolder(String folder) {
		this.folder = folder.trim();
	}
	/****************************************************
	 * Method name		: getName [GETTER]
	 * Return type		: Function
	 * Decription		: 
	 * Created date		: Jun 4, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public String getName() {
		return name;
	}
	/****************************************************
	 * Method name		: setName [SETTER]
	 * Return type		: void
	 * Decription		: 
	 * Created date		: Jun 4, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setName(String name) {
		this.name = name.trim();
	}

	/****************************************************
	 * Method name		: getId [GETTER]
	 * Return type		: Function
	 * Decription		: 
	 * Created date		: Jun 22, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public int getId() {
		return id;
	}

	/****************************************************
	 * Method name		: setId [SETTER]
	 * Return type		: void
	 * Decription		: 
	 * Created date		: Jun 22, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setId(int id) {
		this.id = id;
	}


	public List<FuncTable> getList() {
		this.dataOfFuncs();
		return list;
	}


	public void setList(List<FuncTable> list) {
		this.list = list;
	}
	
	
}
